﻿// =================================================================== 
// 项目说明
//====================================================================
// 文件： ERP_CusCargo.cs
// 创建时间：2016/11/26
// 负责人：
// ===================================================================
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using CRMMis.Model;
using CRMMis.IDAL;


namespace MSSQLDAL
{
    /// <summary>
    /// 数据层实例化接口类  商品目录表.
    /// </summary>
    public partial class ERP_CusCargoDAL : IERP_CusCargoDAL
    {
		//此处写查询数据的sql语句，


        //if QueryDataString==""，DAL层的GetList，GetRecordCount等函数将直接存取ERP_CusCargo表


        //if QueryDataString!=""，则DAL层的GetList，GetRecordCount等函数将存取该SQL语句，取代表
		//注意：所写sql语句执行结果的字段必须是唯一的

		private const string QueryDataString = @"SELECT
                a.CargoId,
                a.CargoName,
                a.Spell,
                a.ByName,
                a.CargoCode,
                a.Spec,
                a.Version,
                a.TypeId,
                a.UseId,
                a.Area,
                a.Unit,
                a.IsImported,
                a.Pic,
                a.InUser,
                a.InDate,
                a.ProduceName,
                a.State,
                b.Type_Name,
                c.Use_Name,
                CASE a.IsImported WHEN 0 THEN '国产' ELSE '进口' END IsImport,
                (select top 1 Supplyprice from ERP_CusCargo_Detail where a.CargoId=CargoId order by DetailId desc) Supplyprice,
                (select top 1 State from ERP_Cargo_Detail where a.CargoId=CargoId order by DetailId desc) Detail_State,
                a.CustomId,
                f.CustomerName
                FROM ERP_CusCargo a 
                LEFT JOIN ERP_Type b ON a.TypeId=b.Type_Id
                LEFT JOIN ERP_Use c ON a.UseId=c.Use_ID
                LEFT JOIN ERP_Customer f on a.CustomId=f.CustomerId";


        /// <summary>
		/// 得到数据表ERP_CusCargo所有记录，可排序
		/// </summary>
        /// <param name="sWhere">条件字符串</param>
        /// <param name="sOder">排序字段</param>
		/// <returns>DataSet</returns>
		public DataSet GetCargoList(string sWhere, string sOder)
        {
            string sqlTab = @" ( SELECT a.*,
b.CustomerId,
b.CusCargoId,
a.CargoName as BsCargoName,
a.CargoId as BsCargoId
FROM ERP_CusCargo a 
INNER JOIN ERP_CgRelations b on b.CusCargoId = a.CargoId 
INNER JOIN ERP_Cargo c on b.CargoId = c.CargoId
) SQLA";

            string sqlStr = "select * from " + sqlTab;

            if (sWhere.Length > 0)
                sqlStr += " where " + sWhere;
            if (sOder.Length > 0)
                sqlStr += " order by " + sOder;

            WriteLog("ERP_CusCargoDAL", "GetList(string sWhere, string sOder)", sqlStr);

            return SqlHelper.ExecuteDataset(Conn.SqlConnStr, CommandType.Text, sqlStr);
        }

    }
}
